package cn.Controller;

import cn.Entity.Employee;
import cn.Service.IEmployeeService;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;

@Controller
public class PoiController {
    @Autowired
    private IEmployeeService employeeService;

    Integer rowIndex = 0;

    @ResponseBody
    @PostMapping("/uploadExcelFile")
    public JSONObject uploadExcelFile( MultipartFile excelFile) throws IOException {
        JSONObject result = new JSONObject();
        result.put("PathVariable" ,excelFile.getName());
//      读取上传的文件
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelFile.getInputStream());
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        System.out.println("读取Excel文件："+excelFile);
        System.out.println("读物Sheet："+sheet.getSheetName());

        sheet.forEach(i->{
            System.out.println(i.getCell(0).getStringCellValue());
            System.out.println(i.getCell(1).getNumericCellValue());
            System.out.println(i.getCell(2).getNumericCellValue());
            Employee employee = new Employee();
            employee.setName(i.getCell(0).getStringCellValue());
            employee.setAge( (int)i.getCell(1).getNumericCellValue());
            employee.setSalary((float) i.getCell(2).getNumericCellValue());
//          将Excel里的数据遍历出来放入DB
            employeeService.saveEmployee(employee);
        });

        xssfWorkbook.close();
        return result;
    }

    /**
     * 下载Excel
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("/listAllEmployeeOfExcel")
    public void listAllEmployeeOfExcel(HttpServletRequest request , HttpServletResponse response) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");
//      读取employee、放入Excel
        employeeService.findAll().forEach(i->{
        XSSFRow row = sheet.createRow(rowIndex++);
        XSSFCell cell = row.createCell(0);
        XSSFCell cell2 = row.createCell(1);
        XSSFCell cell3 = row.createCell(2);
        cell.setCellValue(i.getName());
        cell2.setCellValue(i.getAge());
        cell3.setCellValue(i.getSalary());

        });
//      response输出流设置
        response.reset();
        response.setHeader("Content-disposition","attachment; filename=Employee.xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        workbook.write(response.getOutputStream());
        workbook.close();

    }
}
